package com.ruoyi.web.service.impl;

import com.gitee.deeround.jdbchelper.DbAction;
import com.gitee.deeround.jdbchelper.in.ParameterDirection;
import com.gitee.deeround.jdbchelper.in.ParameterType;
import com.gitee.deeround.jdbchelper.in.SqlParameter;
import com.gitee.deeround.jdbchelper.out.DataType;
import com.gitee.deeround.jdbchelper.out.PageList;
import com.gitee.deeround.jdbchelper.resultSetExtractors.CustomApply;
import com.ruoyi.core.entity.BaseMap;
import com.ruoyi.core.entity.PageInfo;
import com.ruoyi.core.entity.SortByType;
import com.ruoyi.core.entity.SortInfo;
import com.ruoyi.core.util.IdUtil;
import com.ruoyi.web.context.RequestContextHolder;
import com.ruoyi.web.domain.SysMod;
import com.ruoyi.web.domain.SysModColumn;
import com.ruoyi.web.entity.system.SqlBuilder;
import com.ruoyi.web.entity.system.SysModInfoVo;
import com.ruoyi.web.service.SysModDataService;
import com.ruoyi.web.service.SysModService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service("SysModDataService")
public class SysModDataServiceImpl implements SysModDataService {

    @Autowired
    private DbAction dbAction;

    @Autowired
    private SysModService sysModService;

    protected SysModInfoVo getModInfo(String modId) {
        return sysModService.getInfoById(modId);
    }

    protected SysMod getMod(String modId) {
        return sysModService.getById(modId);
    }

    @Override
    public PageInfo<BaseMap> page(String modId, BaseMap entity) {

        SysModInfoVo modInfo = getModInfo(modId);

        SqlBuilder sqlBuilder = getSqlBuilder(modInfo, entity);

        Integer pageNum = entity.getInteger("pageNum", 1);
        Integer pageSize = entity.getInteger("pageSize", 10);

        PageList<BaseMap> list = dbAction.queryForPage(sqlBuilder.getFullSql(), pageNum, pageSize, new CustomApply<BaseMap>() {
            @Override
            public BaseMap apply(Map<String, Object> map) {
                return new BaseMap(map);
            }
        }, sqlBuilder.getParameters());

        PageInfo<BaseMap> list1 = new PageInfo<>(list.getPageNum(), list.getPageSize(), list.getList(), list.getTotal());

        for (SysModColumn column : modInfo.getColumns()) {

            if (!StringUtils.isEmpty(column.getSubTableName())) {

                List<String> ids = list1.getList().stream().map(x -> x.getString(modInfo.getPrimaryKey())).collect(Collectors.toList());
                String idss = "'" + String.join("','", ids) + "'";
                List<BaseMap> list2 = dbAction.queryForListObject("select * from " + column.getSubTableName() + " where " + modInfo.getPrimaryKey() + " in (" + idss + ")", new CustomApply<BaseMap>() {
                    @Override
                    public BaseMap apply(Map<String, Object> map) {
                        return new BaseMap(map);
                    }
                });
                for (BaseMap baseMap : list1.getList()) {
                    baseMap.put(
                            column.getColumnName(),
                            list2.stream().
                                    filter(x -> x.getString(modInfo.getPrimaryKey()).equals(baseMap.getString(modInfo.getPrimaryKey())))
                                    .map(x -> x.getString(column.getColumnName()))
                                    .collect(Collectors.toList()));
                }

            } else if ("1".equals(column.getIsMultiple())) {

                for (BaseMap baseMap : list1.getList()) {
                    baseMap.put(
                            column.getColumnName(),
                            baseMap.isEmpty(column.getColumnName()) ? null : baseMap.getString(column.getColumnName()).split(",")
                    );

                }


            }
        }

        return list1;
    }

    @Override
    public List<BaseMap> list(String modId, BaseMap entity) {

        SysModInfoVo modInfo = getModInfo(modId);

        SqlBuilder sqlBuilder = getSqlBuilder(modInfo, entity);

        List<BaseMap> list = dbAction.queryForListObject(sqlBuilder.getFullSql(), new CustomApply<BaseMap>() {
            @Override
            public BaseMap apply(Map<String, Object> map) {
                return new BaseMap(map);
            }
        }, sqlBuilder.getParameters());

        return list;
    }

    @Transactional(rollbackFor = Throwable.class)
    @Override
    public BaseMap add(String modId, BaseMap entity) {

        SysModInfoVo modInfo = getModInfo(modId);

        addData(modInfo, entity, null, null);

        for (SysModColumn column : modInfo.getColumns().stream().filter(x -> !StringUtils.isEmpty(x.getSubTableName())).collect(Collectors.toList())) {
            List val = (List) entity.getObject(column.getColumnName());
            List<Object[]> ps = new ArrayList<>();
            for (Object o : val) {
                ps.add(new Object[]{entity.getString(modInfo.getPrimaryKey()), o});
            }
            dbAction.batchUpdate("insert into " + column.getSubTableName() + "(" + modInfo.getPrimaryKey() + "," + column.getColumnName() + ") values(?,?)", ps);
        }

        return getById(modId, entity);
    }

    @Transactional(rollbackFor = Throwable.class)
    @Override
    public BaseMap edit(String modId, BaseMap entity) {
        SysModInfoVo modInfo = getModInfo(modId);

        updateData(modInfo, entity, null, null);

        for (SysModColumn column : modInfo.getColumns().stream().filter(x -> !StringUtils.isEmpty(x.getSubTableName())).collect(Collectors.toList())) {
            //先删除
            dbAction.update("delete from " + column.getSubTableName() + " where " + modInfo.getPrimaryKey() + "=?", new Object[]{entity.getString(modInfo.getPrimaryKey())});
            //后新增
            List val = (List) entity.getObject(column.getColumnName());
            List<Object[]> ps = new ArrayList<>();
            for (Object o : val) {
                ps.add(new Object[]{entity.getString(modInfo.getPrimaryKey()), o});
            }
            dbAction.batchUpdate("insert into " + column.getSubTableName() + "(" + modInfo.getPrimaryKey() + "," + column.getColumnName() + ") values(?,?)", ps);
        }

        return getById(modId, entity);
    }

    @Transactional(rollbackFor = Throwable.class)
    @Override
    public void delete(String modId, BaseMap entity) {

        SysModInfoVo modInfo = getModInfo(modId);

        for (SysModColumn column : modInfo.getColumns().stream().filter(x -> !StringUtils.isEmpty(x.getSubTableName())).collect(Collectors.toList())) {
            dbAction.update("delete from " + column.getSubTableName() + " where " + modInfo.getPrimaryKey() + "=?", new Object[]{entity.getString(modInfo.getPrimaryKey())});
        }
        dbAction.update("delete from " + modInfo.getTableName() + " where " + modInfo.getPrimaryKey() + "=?", new Object[]{entity.getString(modInfo.getPrimaryKey())});
    }

    @Override
    public BaseMap getById(String modId, BaseMap entity) {

        SysModInfoVo modInfo = getModInfo(modId);

        BaseMap result = dbAction.queryForObject("select * from " + modInfo.getTableName() + " where " + modInfo.getPrimaryKey() + "=?", new CustomApply<BaseMap>() {
            @Override
            public BaseMap apply(Map<String, Object> map) {
                return new BaseMap(map);
            }
        }, entity.get(modInfo.getPrimaryKey()));

        return result;
    }


    protected void addData(SysModInfoVo modInfo, BaseMap data, List<String> ignoreColumns, BaseMap fixedData) {
        createAdd(data, modInfo.getPrimaryKey());
        List<SysModColumn> columns = modInfo.getColumns();
        List<SqlParameter> ps = new ArrayList<>();
        for (int i = 0; i < columns.size(); i++) {
            SysModColumn column = columns.get(i);

            //从表字段排除
            if (!StringUtils.isEmpty(column.getSubTableName())) {
                continue;
            }

            //忽略字段
            if (ignoreColumns != null && ignoreColumns.contains(column.getColumnName())) {
                continue;
            }

            //参数
            Object value = data.get(column.getColumnName());

            //处理下多选情况
            if (value != null && value instanceof List) {
                List listTemp = (List) value;
                if (listTemp.size() == 0) {
                    value = null;
                } else {
                    value = listTemp.stream().collect(Collectors.joining(","));
                }
            }

            //固定值
            if (fixedData != null && fixedData.containsKey(column.getColumnName())) {
                value = fixedData.get(column.getColumnName());
            }

            ps.add(new SqlParameter(column.getColumnName(), value, ParameterType.valueOf(column.getDataType()), ParameterDirection.Input));
        }

        //判断公共字段
        String[] commonColumns = {"create_by", "create_time", "create_dept", "update_by", "update_time"};
        for (String column : commonColumns) {
            if (ps.stream().noneMatch(x -> x.getParameterName().equals(column))) {
                ps.add(new SqlParameter(column, data.getObject(column)));
            }
        }

        String sql = "insert into " + modInfo.getTableName() + "(" + ps.stream().map(SqlParameter::getParameterName).collect(Collectors.joining(",")) + ") values(" + ps.stream().map(x -> "?").collect(Collectors.joining(",")) + ")";
        dbAction.execute(sql, ps);
    }

    protected void createAdd(BaseMap data, String primaryKey) {
        Date now = new Date();
        data.put("create_dept", RequestContextHolder.currentDeptId());
        data.put("create_time", now);
        data.put("create_by", RequestContextHolder.currentUserId());
        data.put("update_time", now);
        data.put("update_by", RequestContextHolder.currentUserId());
        if (data.isEmpty(primaryKey)) {
            data.put(primaryKey, IdUtil.sequenceId());
        }
    }

    protected void updateData(SysModInfoVo modInfo, BaseMap data, List<String> ignoreColumns, BaseMap fixedData) {
        createUpdate(data);
        List<SysModColumn> columns = modInfo.getColumns();
        List<SqlParameter> ps = new ArrayList<>();
        SysModColumn primaryKeyColumn = null;
        for (int i = 0; i < columns.size(); i++) {
            SysModColumn column = columns.get(i);

            //从表字段排除
            if (!StringUtils.isEmpty(column.getSubTableName())) {
                continue;
            }

            //忽略字段
            if (ignoreColumns != null && ignoreColumns.contains(column.getColumnName())) {
                continue;
            }

            //判断主键
            if (column.getColumnName().equalsIgnoreCase(modInfo.getPrimaryKey())) {
                primaryKeyColumn = column;
                continue;
            }
            //"create_dept","create_time","create_by"
            if ("create_dept".equalsIgnoreCase(column.getColumnName()) || "create_time".equalsIgnoreCase(column.getColumnName()) || "create_by".equalsIgnoreCase(column.getColumnName())) {
                continue;
            }

            //参数
            Object value = data.get(column.getColumnName());

            //处理下多选情况
            if (value != null && value instanceof List) {
                List listTemp = (List) value;
                if (listTemp.size() == 0) {
                    value = null;
                } else {
                    value = listTemp.stream().collect(Collectors.joining(","));
                }
            }

            //固定值
            if (fixedData != null && fixedData.containsKey(column.getColumnName())) {
                value = fixedData.get(column.getColumnName());
            }
            
            ps.add(new SqlParameter(column.getColumnName(), value, ParameterType.valueOf(column.getDataType()), ParameterDirection.Input));
        }

        //判断公共字段
        String[] commonColumns = {"update_by", "update_time"};
        for (String column : commonColumns) {
            if (ps.stream().noneMatch(x -> x.getParameterName().equals(column))) {
                ps.add(new SqlParameter(column, data.getObject(column)));
            }
        }

        String sql = "update " + modInfo.getTableName() + " set " + ps.stream().map(x -> x.getParameterName() + "=?").collect(Collectors.joining(",")) + " where " + modInfo.getPrimaryKey() + "=?";
        ps.add(new SqlParameter(primaryKeyColumn.getColumnName(), data.get(primaryKeyColumn.getColumnName()), ParameterType.valueOf(primaryKeyColumn.getDataType()), ParameterDirection.Input));
        dbAction.execute(sql, ps);
    }

    protected void createUpdate(BaseMap data) {
        Date now = new Date();
        //data.put("create_dept", RequestContextHolder.currentDeptId());
        //data.put("create_time", now);
        //data.put("create_by", RequestContextHolder.currentUserId());
        data.put("update_time", now);
        data.put("update_by", RequestContextHolder.currentUserId());
    }

    protected SqlBuilder getSqlBuilder(SysModInfoVo modInfo, BaseMap search) {

        List<SysModColumn> columns = modInfo.getColumns();
        List<Object> ps1 = new ArrayList<>();
        StringBuilder sb1 = new StringBuilder();
        StringBuilder sbTotalRow = new StringBuilder();
        StringBuilder sbWhere = new StringBuilder();


        //SQL语句主体
//        StringBuilder sbColumn = new StringBuilder();
//        for (int i = 0; i < columns.size(); i++) {
//            SysModColumn column = columns.get(i);
//            //子表字段，列表查询时，不查询
//            if (!StringUtils.isEmpty(column.getSubTableName())) {
//                continue;
//            }
//
//            sbColumn.append(",").append(column.getColumnName());
//        }
//        sb1.append(" select ").append(sbColumn.substring(1));

        //为了简化逻辑判断，暂时先查询出所有字段
        sb1.append(" select *");
        sb1.append(" from " + modInfo.getTableName());
        sb1.append(" where 1=1");

        //合计行
        List<SysModColumn> sumColumns = columns.stream().filter(x -> !StringUtils.isEmpty(x.getSumType()) && !"none".equals(x.getSumType())).collect(Collectors.toList());
        if (sumColumns.size() > 0) {
            sbTotalRow.append("select ");
        }
        for (int i = 0; i < sumColumns.size(); i++) {
            SysModColumn column = sumColumns.get(i);
            String sumColumnName = column.getColumnName();
            if (!StringUtils.isEmpty(column.getSumColumnName())) {
                sumColumnName = column.getSumColumnName();
            }
            switch (column.getSumType()) {
                case "sum": {
                    sbTotalRow.append("sum(" + sumColumnName + ") " + column.getColumnName());
                }
                break;
                case "count": {
                    sbTotalRow.append("count(" + sumColumnName + ") " + column.getColumnName());
                }
                break;
                case "avg": {
                    sbTotalRow.append("avg(" + sumColumnName + ") " + column.getColumnName());
                }
                break;
                case "max": {
                    sbTotalRow.append("max(" + sumColumnName + ") " + column.getColumnName());
                }
                break;
                case "min": {
                    sbTotalRow.append("min(" + sumColumnName + ") " + column.getColumnName());
                }
                break;
                default:
                    throw new IllegalStateException("Unexpected value: " + column.getSumType());
            }
            if (i != sumColumns.size() - 1) {
                sbTotalRow.append(",");
            }
        }
        if (sumColumns.size() > 0) {
            sbTotalRow.append(" from " + modInfo.getTableName());
            sbTotalRow.append(" where 1=1");
        }


        //查询条件
        for (SysModColumn column : columns) {
            if (!StringUtils.isEmpty(column.getQueryType()) && !"none".equals(column.getQueryType())) {

                //是否传入该字段查询条件的值
                if ("between".equals(column.getQueryType())) {
                    if (search.isEmpty(column.getColumnName() + "$1") && search.isEmpty(column.getColumnName() + "$2")) {
                        continue;
                    }
                } else if (search.isEmpty(column.getColumnName())) {
                    continue;
                }

                Object paramValue = search.get(column.getColumnName());

                //处理下多选情况
                if (paramValue != null && paramValue instanceof List) {
                    List listTemp = (List) paramValue;
                    if (listTemp.size() == 0) {
                        continue;
                    } else {
                        paramValue = listTemp.stream().collect(Collectors.joining(","));
                    }
                }

                //查询类型：不查询；精准查询；模糊查询；范围查询
                switch (column.getQueryType()) {
                    case "eq": {
                        if ("1".equals(column.getIsQueryMultiple())) {
                            String[] ss = paramValue.toString().split(",");
                            sbWhere.append(" and (");
                            for (int i = 0; i < ss.length; i++) {
                                if ("1".equals(column.getIsMultiple())) {
                                    if (i == 0) {
                                        sbWhere.append(" instr(concat(" + column.getColumnName() + ",','),concat(?,','))>0");
                                    } else {
                                        sbWhere.append(" or instr(concat(" + column.getColumnName() + ",','),concat(?,','))>0");
                                    }
                                    ps1.add(ss[i]);
                                } else {
                                    if (i == 0) {
                                        sbWhere.append(" " + column.getColumnName() + "=?");
                                    } else {
                                        sbWhere.append(" or " + column.getColumnName() + "=?");
                                    }
                                    ps1.add(ss[i]);
                                }
                            }
                            sbWhere.append(" )");
                        } else if ("1".equals(column.getIsMultiple())) {
                            sbWhere.append(" and instr(concat(" + column.getColumnName() + ",','),concat(?,','))>0");
                            ps1.add(paramValue);
                        } else {
                            sbWhere.append(" and " + column.getColumnName() + "=?");
                            ps1.add(paramValue);
                        }
                    }
                    break;
                    case "like": {
                        sbWhere.append(" and " + column.getColumnName() + " like concat(concat('%',?),'%')");
                        ps1.add(paramValue);
                    }
                    break;
                    case "between": {
                        String s = search.getString(column.getColumnName() + "$1");
                        String e = search.getString(column.getColumnName() + "$2");
                        //针对日期格式特殊处理，补全日期格式
                        if (DataType.Date.toString().equals(column.getDataType())) {
                            if ("yyyy".equals(column.getQueryControlFormat())) {
                                if (!StringUtils.isEmpty(s)) {
                                    s += "-01-01 00:00:00";
                                }
                                if (!StringUtils.isEmpty(e)) {
                                    e += "-12-31 23:59:59";
                                }
                            } else if ("yyyy-MM".equals(column.getQueryControlFormat())) {
                                if (!StringUtils.isEmpty(s)) {
                                    s += "-01 00:00:00";
                                }
                                if (!StringUtils.isEmpty(e)) {
                                    e += "-31 23:59:59";
                                }
                            } else if ("yyyy-MM-dd".equals(column.getQueryControlFormat())) {
                                if (!StringUtils.isEmpty(s)) {
                                    s += " 00:00:00";
                                }
                                if (!StringUtils.isEmpty(e)) {
                                    e += " 23:59:59";
                                }
                            }
                        }
                        if (!StringUtils.isEmpty(s)) {
                            sbWhere.append(" and " + column.getColumnName() + ">=?");
                            ps1.add(s);
                        }
                        if (!StringUtils.isEmpty(e)) {
                            sbWhere.append(" and " + column.getColumnName() + "<=?");
                            ps1.add(e);
                        }
                    }
                    break;
                    default:
                        break;
                }
            }
        }

        SqlBuilder sqlBuilder = new SqlBuilder();

        //排序
        if (!search.isEmpty("sortName") && !search.isEmpty("sortBy")) {
            List<SortInfo> sorts = new ArrayList<>();
            sorts.add(new SortInfo(search.getString("sortName"), SortByType.desc.toString().equals(search.getString("sortBy")) ? SortByType.desc : SortByType.asc));
            sqlBuilder.setSorts(sorts);
        }

        //分页信息
        Integer pageNum = search.getInteger("pageNum", 1);
        Integer pageSize = search.getInteger("pageSize", 10);
        PageInfo pageInfo = new PageInfo(pageNum, pageSize);
        sqlBuilder.setPageInfo(pageInfo);

        sqlBuilder.setTableName(modInfo.getTableName());
        sqlBuilder.setSql(sb1.toString());
        sqlBuilder.setWhere(sbWhere.toString());
        sqlBuilder.setTotalSql(sbTotalRow.toString());
        sqlBuilder.setParameters(ps1.toArray());

        log.debug("生成的SQL：{}", sqlBuilder.getFullSql());

        return sqlBuilder;
    }


}
